#!/bin/bash

# =====================================================
# 技术文档管理 - 数据库快速初始化脚本
# =====================================================
# 用途：一键初始化数据库（完整版）
# 使用方法：chmod +x 00-quick-init.sh && ./00-quick-init.sh
# =====================================================

set -e  # 遇到错误立即退出

# 颜色输出
RED='\033[0;31m'
GREEN='\033[0;32m'
YELLOW='\033[1;33m'
BLUE='\033[0;34m'
NC='\033[0m' # No Color

# 打印信息
info() {
    echo -e "${BLUE}ℹ️  $1${NC}"
}

success() {
    echo -e "${GREEN}✅ $1${NC}"
}

warning() {
    echo -e "${YELLOW}⚠️  $1${NC}"
}

error() {
    echo -e "${RED}❌ $1${NC}"
}

# 打印标题
print_header() {
    echo ""
    echo "========================================"
    echo "  技术文档管理 - 数据库快速初始化"
    echo "========================================"
    echo ""
}

# 检查 PostgreSQL 是否安装
check_postgresql() {
    info "检查 PostgreSQL 是否已安装..."
    if ! command -v psql &> /dev/null; then
        error "未检测到 PostgreSQL，请先安装 PostgreSQL 14+"
        exit 1
    fi
    
    local version=$(psql --version | awk '{print $3}')
    success "PostgreSQL 版本: $version"
}

# 读取配置
read_config() {
    info "读取数据库配置..."
    
    # 默认配置
    DB_HOST="${DB_HOST:-localhost}"
    DB_PORT="${DB_PORT:-5432}"
    DB_NAME="${DB_NAME:-doc_management}"
    DB_USER="${DB_USER:-postgres}"
    
    # 如果存在 .env 文件，尝试读取
    if [ -f "../../.env" ]; then
        info "从 .env 文件读取配置..."
        export $(grep -v '^#' ../../.env | xargs)
    fi
    
    # 读取密码（如果未设置）
    if [ -z "$DB_PASSWORD" ]; then
        read -sp "$(echo -e ${YELLOW}'请输入数据库密码: '${NC})" DB_PASSWORD
        echo ""
        export DB_PASSWORD
    fi
    
    echo ""
    echo "数据库配置："
    echo "  主机: $DB_HOST"
    echo "  端口: $DB_PORT"
    echo "  数据库: $DB_NAME"
    echo "  用户: $DB_USER"
    echo ""
}

# 询问是否继续
confirm() {
    read -p "$(echo -e ${YELLOW}'是否继续？(y/n): '${NC})" -r
    echo
    if [[ ! $REPLY =~ ^[Yy]$ ]]; then
        warning "操作已取消"
        exit 0
    fi
}

# 创建数据库
create_database() {
    info "检查数据库是否存在..."
    
    # 检查数据库是否存在
    DB_EXISTS=$(PGPASSWORD=$DB_PASSWORD psql -h $DB_HOST -p $DB_PORT -U $DB_USER -tAc "SELECT 1 FROM pg_database WHERE datname='$DB_NAME'" 2>/dev/null || echo "")
    
    if [ "$DB_EXISTS" = "1" ]; then
        warning "数据库 '$DB_NAME' 已存在"
        read -p "$(echo -e ${YELLOW}'是否删除并重新创建？(y/n): '${NC})" -r
        echo
        if [[ $REPLY =~ ^[Yy]$ ]]; then
            info "删除现有数据库..."
            PGPASSWORD=$DB_PASSWORD psql -h $DB_HOST -p $DB_PORT -U $DB_USER -c "DROP DATABASE IF EXISTS $DB_NAME;" 2>/dev/null || {
                error "删除数据库失败，请确保没有活动连接"
                exit 1
            }
            success "数据库已删除"
        else
            warning "将在现有数据库上执行初始化（会删除现有数据！）"
            confirm
            return 0
        fi
    fi
    
    # 创建数据库
    info "创建数据库 '$DB_NAME'..."
    PGPASSWORD=$DB_PASSWORD psql -h $DB_HOST -p $DB_PORT -U $DB_USER -c "CREATE DATABASE $DB_NAME ENCODING 'UTF8';" 2>/dev/null || {
        error "创建数据库失败"
        exit 1
    }
    success "数据库创建成功"
}

# 执行 SQL 脚本
run_sql_script() {
    local script_file=$1
    local script_name=$2
    
    if [ ! -f "$script_file" ]; then
        error "脚本文件不存在: $script_file"
        exit 1
    fi
    
    info "执行 $script_name..."
    PGPASSWORD=$DB_PASSWORD psql -h $DB_HOST -p $DB_PORT -U $DB_USER -d $DB_NAME -f "$script_file" || {
        error "$script_name 执行失败"
        exit 1
    }
    success "$script_name 执行成功"
}

# 生成测试数据
generate_seed_data() {
    info "生成测试数据..."
    
    read -p "$(echo -e ${YELLOW}'是否生成测试数据？(y/n): '${NC})" -r
    echo
    if [[ ! $REPLY =~ ^[Yy]$ ]]; then
        warning "跳过测试数据生成"
        return 0
    fi
    
    run_sql_script "02-database-seed-complete.sql" "测试数据脚本"
}

# 验证数据
verify_data() {
    info "验证数据..."
    
    echo ""
    echo "数据统计："
    
    # 统计表数量
    TABLE_COUNT=$(PGPASSWORD=$DB_PASSWORD psql -h $DB_HOST -p $DB_PORT -U $DB_USER -d $DB_NAME -tAc "SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = 'public' AND table_type = 'BASE TABLE';")
    echo "  - 表数量: $TABLE_COUNT"
    
    # 统计分类数量
    CATEGORY_COUNT=$(PGPASSWORD=$DB_PASSWORD psql -h $DB_HOST -p $DB_PORT -U $DB_USER -d $DB_NAME -tAc "SELECT COUNT(*) FROM doc_categories;" 2>/dev/null || echo "0")
    echo "  - 分类数量: $CATEGORY_COUNT"
    
    # 统计文档数量
    DOC_COUNT=$(PGPASSWORD=$DB_PASSWORD psql -h $DB_HOST -p $DB_PORT -U $DB_USER -d $DB_NAME -tAc "SELECT COUNT(*) FROM documents;" 2>/dev/null || echo "0")
    echo "  - 文档总数: $DOC_COUNT"
    
    # 统计用户数量
    USER_COUNT=$(PGPASSWORD=$DB_PASSWORD psql -h $DB_HOST -p $DB_PORT -U $DB_USER -d $DB_NAME -tAc "SELECT COUNT(*) FROM users;" 2>/dev/null || echo "0")
    echo "  - 用户数量: $USER_COUNT"
    
    # 检查 file_metadata 表是否存在
    FILE_TABLE_EXISTS=$(PGPASSWORD=$DB_PASSWORD psql -h $DB_HOST -p $DB_PORT -U $DB_USER -d $DB_NAME -tAc "SELECT 1 FROM information_schema.tables WHERE table_name = 'file_metadata';" 2>/dev/null || echo "")
    if [ "$FILE_TABLE_EXISTS" = "1" ]; then
        success "  - file_metadata 表已创建"
        
        # 检查 upload_source 字段
        UPLOAD_SOURCE_EXISTS=$(PGPASSWORD=$DB_PASSWORD psql -h $DB_HOST -p $DB_PORT -U $DB_USER -d $DB_NAME -tAc "SELECT 1 FROM information_schema.columns WHERE table_name = 'file_metadata' AND column_name = 'upload_source';" 2>/dev/null || echo "")
        if [ "$UPLOAD_SOURCE_EXISTS" = "1" ]; then
            success "  - upload_source 字段已添加"
        else
            warning "  - upload_source 字段未找到"
        fi
    else
        warning "  - file_metadata 表未找到"
    fi
    
    echo ""
    success "数据验证完成"
}

# 打印后续步骤
print_next_steps() {
    echo ""
    echo "========================================"
    echo "  初始化完成！"
    echo "========================================"
    echo ""
    echo "后续步骤："
    echo ""
    echo "1. 查看生成的数据："
    echo "   psql -h $DB_HOST -p $DB_PORT -U $DB_USER -d $DB_NAME"
    echo "   > SELECT COUNT(*) FROM documents;"
    echo ""
    echo "2. 启动后端服务："
    echo "   cd ../../doc-management-api"
    echo "   ./mvnw spring-boot:run"
    echo ""
    echo "3. 访问 API："
    echo "   curl http://localhost:8080/doc/api/v1/documents"
    echo ""
    echo "4. 查看 API 文档："
    echo "   http://localhost:8080/doc/swagger-ui.html"
    echo ""
    success "数据库初始化完成！🎉"
    echo ""
}

# 主函数
main() {
    print_header
    check_postgresql
    read_config
    confirm
    create_database
    
    # 按顺序执行脚本
    run_sql_script "01-database-init-complete.sql" "核心表初始化脚本"
    run_sql_script "03-database-migration-minio.sql" "MinIO 迁移脚本"
    run_sql_script "04-database-migration-add-upload-source.sql" "upload_source 迁移脚本"
    
    generate_seed_data
    verify_data
    print_next_steps
}

# 执行主函数
main

